package tt.dz.biz.dao.financial;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.common.BusinessException;
import tt.dz.vo.IdentityAccountVO;
import tt.dz.vo.MerchantSubjectAccVO;
import tt.dz.vo.MerchantVO;
import tt.dz.vo.SubjectVO;

@Repository("merchantDAO")
public class MerchantDAO
  extends BaseDAO
{
  private static final String LIST_ALL_MERCHANT = "select  mm.merchant_code, mm.signal_code,mm.short_name,mm.name,mm.is_self,mm.bal_type,mm.address,mm.post,mm.owner,mm.id_no,mm.telphone,mm.contact_name,mm.state,mm.district_code,mm.check_date,mm.created_by,mm.created_time,mm.updated_by,mm.updated_time,mm.is_valid,mm.provider_type from ms_merchant mm ";
  private static final String INSERT_MERCHANT = "insert into ms_merchant(merchant_code,signal_code,short_name,name,is_self,bal_type,address,post,owner,id_no,telphone,contact_name,state,district_code,check_date,created_by,created_time,updated_by,updated_time,is_valid,provider_type)  values(:merchantCode,:signalCode,:shortName,:name,:isSelf,:balType,:address,:post,:owner,:idNo,:telphone,:contactName,:state,:districtCode,:checkDate,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid,:providerType )";
  private static final String UPDATE_MERCHANT = "update  ms_merchant set short_name = :shortName,signal_code=:signalCode,name = :name,is_self = :isSelf,bal_type = :balType,address = :address,post = :post,owner = :owner,id_no = :idNo,telphone = :telphone,contact_name = :contactName,state = :state,district_code = :districtCode,check_date = :checkDate,created_by = :createdBy,created_time = :createdTime,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid,provider_type = :providerType where merchant_code = :merchantCode ";
  private static final String INSERT_MERCHANT_SUBJECT = "INSERT INTO ms_merchant_subject_acc(merchant_code,sub_subject_code,acct_bal) VALUES (:merchantCode, :subjectCode,0);";
  private static final String INSERT_MERCHANT_SUBJECT_BY_OBJECT = "INSERT INTO ms_merchant_subject_acc(merchant_code,sub_subject_code,acct_bal,last_bal) VALUES (:merchantCode, :subSubjectCode,:acctBal,:lastBal)";
  private static final String LIST_ALL_SUBJECT = "select  a.subject_code from ms_subject as a ";
  private static final String QUERY_INCREMENT_MERCHANT = "select mm.merchant_code,mm.signal_code,mm.short_name,mm.name,mm.is_self,mm.bal_type,mm.address,mm.post,mm.owner,mm.id_no,mm.telphone,mm.contact_name,mm.state,mm.district_code,mm.check_date,mm.created_by,mm.created_time,mm.updated_by,mm.updated_time,mm.is_valid,mm.provider_type from ms_merchant mm  where 1 = 1 ";
  private static final String QUERY_MERCH_BY_IDENTITY_CARD_NO = "select  mm.merchant_code,mm.short_name,mm.name,mm.is_self,mm.bal_type,mm.address,mm.post,mm.owner,mm.id_no,mm.telphone,mm.contact_name,mm.state,mm.district_code,mm.check_date,mm.created_by,mm.created_time,mm.updated_by,mm.updated_time,mm.is_valid,mm.provider_type  from ms_user_base  t ,  ms_identity_account a , ms_merchant mm  where  t.user_id = a.user_id and  t.enterprise_code= mm.merchant_code and a.card_no = :cardNo and t.is_valid=1  and mm.is_valid=1 and a.state=1";
  private static final String LIST_ALL_VIRTUAL_MERCHANT = "select  a.merchant_code,a.short_name,a.name,a.is_self,a.bal_type,a.address,a.post,a.owner,a.id_no,a.telphone,a.contact_name,a.state,a.district_code,a.check_date,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from ms_merchant as a where a.provider_type = 3 ";
  private static final String QUERY_MERCHANT_CARDNO_BIND_COUNT = "select sum(total) from (  select count(1) as total  from ms_offline_account a where a.merchant_code=:merchantCode  UNION   select count(1) as total from ms_online_account b INNER JOIN ms_user_base ub on ub.user_id = b.user_id  where ub.merchant_code=:merchantCode   UNION  select count(1)  as total from ms_identity_account c where c.merchant_code=:merchantCode   ) t where t.total>0";
  public static final String QUERY_MERCHANT_BY_SIGNAL_CODE = "select  mm.merchant_code, mm.signal_code,mm.short_name,mm.name,mm.is_self,mm.bal_type,mm.address,mm.post,mm.owner,mm.id_no,mm.telphone,mm.contact_name,mm.state,mm.district_code,mm.check_date,mm.created_by,mm.created_time,mm.updated_by,mm.updated_time,mm.is_valid,mm.provider_type from ms_merchant mm  where  mm.signal_code=:signalCode ";
  
  public List<MerchantVO> queryIncrementMerchant(String startTime, String endTime, String providerType)
  {
    StringBuilder builder = new StringBuilder("select mm.merchant_code,mm.signal_code,mm.short_name,mm.name,mm.is_self,mm.bal_type,mm.address,mm.post,mm.owner,mm.id_no,mm.telphone,mm.contact_name,mm.state,mm.district_code,mm.check_date,mm.created_by,mm.created_time,mm.updated_by,mm.updated_time,mm.is_valid,mm.provider_type,mm.alipay,mm.email,mm.transmatic from ms_merchant mm  where 1 = 1 ");
    builder.append(" and ((mm.created_time >= timestamp '" + startTime + "' and mm.created_time <= timestamp '" + endTime + "')");
    
    builder.append(" or (mm.updated_time >= timestamp '" + startTime + "' and mm.updated_time <= timestamp '" + endTime + "'))");
    if (StringUtils.isNotBlank(providerType)) {
      builder.append(" and mm.provider_type = '" + providerType + "' ");
    }
    return queryForList(builder.toString(), null, MerchantVO.class);
  }
  
  public List<MerchantVO> listAllMerchant()
  {
    return queryForList("select  mm.merchant_code, mm.signal_code, mm.short_name,mm.name,mm.is_self,mm.bal_type,mm.address,mm.post,mm.owner,mm.id_no,mm.telphone,mm.contact_name,mm.state,mm.district_code,mm.check_date,mm.created_by,mm.created_time,mm.updated_by,mm.updated_time,mm.is_valid,mm.provider_type,mm.alipay,mm.email,mm.transmatic from ms_merchant mm ", null, MerchantVO.class);
//    return queryForList("select  mm.merchant_code, mm.signal_code,mm.short_name,mm.name,mm.is_self,mm.bal_type,mm.address,mm.post,mm.owner,mm.id_no,mm.telphone,mm.contact_name,mm.state,mm.district_code,mm.check_date,mm.created_by,mm.created_time,mm.updated_by,mm.updated_time,mm.is_valid,mm.provider_type from ms_merchant mm ", null, MerchantVO.class);
  }
  
  public List<MerchantVO> listAllVirtualMerchant()
  {
    return queryForList("select  a.merchant_code,a.short_name,a.name,a.is_self,a.bal_type,a.address,a.post,a.owner,a.id_no,a.telphone,a.contact_name,a.state,a.district_code,a.check_date,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid,mm.alipay,mm.email,mm.transmatic  from ms_merchant as a where a.provider_type = 3 ", null, MerchantVO.class);
  }
  
  public int insertMerchant(MerchantVO merchantVO)
  {
    return save("insert into ms_merchant(merchant_code,signal_code,short_name,name,is_self,bal_type,address,post,owner,id_no,telphone,contact_name,state,district_code,check_date,created_by,created_time,updated_by,updated_time,is_valid,provider_type,alipay,email,transmatic)  values(:merchantCode,:signalCode,:shortName,:name,:isSelf,:balType,:address,:post,:owner,:idNo,:telphone,:contactName,:state,:districtCode,:checkDate,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid,:providerType,:alipay,:email,:transmatic )", merchantVO);
  }
  
  public int updateMerchant(MerchantVO merchantVO)
  {
    return save("update  ms_merchant set short_name = :shortName,signal_code=:signalCode,name = :name,is_self = :isSelf,bal_type = :balType,address = :address,post = :post,owner = :owner,id_no = :idNo,telphone = :telphone,contact_name = :contactName,state = :state,district_code = :districtCode,check_date = :checkDate,created_by = :createdBy,created_time = :createdTime,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid,provider_type = :providerType,alipay=:alipay,email=:email,transmatic=:transmatic where merchant_code = :merchantCode ", merchantVO);
  }
  
  public void insertMerchantSubject(MerchantVO merchantVO)
  {
    List<SubjectVO> subjectList = queryForList("select  a.subject_code from ms_subject as a ", null, SubjectVO.class);
    List<Map<String, String>> batchValues = new ArrayList();
    Iterator<SubjectVO> iterator = subjectList.iterator();
    Map<String, String> paramValue = null;
    SubjectVO subjectVO = null;
    while (iterator.hasNext())
    {
      subjectVO = (SubjectVO)iterator.next();
      if (!"#".equals(subjectVO.getParentSubjectCode()))
      {
        paramValue = new HashMap();
        paramValue.put("subjectCode", subjectVO.getSubjectCode());
        paramValue.put("merchantCode", merchantVO.getMerchantCode());
        batchValues.add(paramValue);
      }
    }
    getJdbcTemplate().batchUpdate("INSERT INTO ms_merchant_subject_acc(merchant_code,sub_subject_code,acct_bal) VALUES (:merchantCode, :subjectCode,0);", (Map[])batchValues.toArray(new Map[subjectList.size()]));
  }
  
  public MerchantVO queryMerchantByCode(String merchantCode)
  {
    try
    {
      MerchantVO param = new MerchantVO();
      param.setMerchantCode(merchantCode);
      StringBuilder sql = new StringBuilder("select mm.merchant_code,mm.signal_code,mm.short_name,mm.name,mm.is_self,mm.bal_type,mm.address,mm.post,mm.owner,mm.id_no,mm.telphone,mm.contact_name,mm.state,mm.district_code,mm.check_date,mm.created_by,mm.created_time,mm.updated_by,mm.updated_time,mm.is_valid,mm.provider_type,mm.alipay,mm.email,mm.transmatic from ms_merchant mm  where 1 = 1 ");
      sql.append(" and mm.merchant_code=:merchantCode ");
      return (MerchantVO)queryForObject(sql.toString(), param, MerchantVO.class);
    }
    catch (EmptyResultDataAccessException e) {}
    return null;
  }
  
  public MerchantVO queryMerchantByIdentifyCardNo(String identifyCardNo)
  {
    try
    {
      IdentityAccountVO param = new IdentityAccountVO();
      param.setCardNo(identifyCardNo);
      return (MerchantVO)queryForObject("select  mm.merchant_code,mm.short_name,mm.name,mm.is_self,mm.bal_type,mm.address,mm.post,mm.owner,mm.id_no,mm.telphone,mm.contact_name,mm.state,mm.district_code,mm.check_date,mm.created_by,mm.created_time,mm.updated_by,mm.updated_time,mm.is_valid,mm.provider_type,mm.alipay,mm.email,mm.transmatic  from ms_user_base  t ,  ms_identity_account a , ms_merchant mm  where  t.user_id = a.user_id and  t.enterprise_code= mm.merchant_code and a.card_no = :cardNo and t.is_valid=1  and mm.is_valid=1 and a.state=1", param, MerchantVO.class);
    }
    catch (EmptyResultDataAccessException e) {}
    return null;
  }
  
  public int[] insertMerchantSubjectAcc(List<MerchantSubjectAccVO> param)
  {
    return batchSave("INSERT INTO ms_merchant_subject_acc(merchant_code,sub_subject_code,acct_bal,last_bal) VALUES (:merchantCode, :subSubjectCode,:acctBal,:lastBal)", param);
  }
  
  public int getMerchantCardNoBinded(String merchantCode)
  {
    try
    {
      MerchantVO param = new MerchantVO();
      param.setMerchantCode(merchantCode);
      return queryForInt("select sum(total) from (  select count(1) as total  from ms_offline_account a where a.merchant_code=:merchantCode  UNION   select count(1) as total from ms_online_account b INNER JOIN ms_user_base ub on ub.user_id = b.user_id  where ub.merchant_code=:merchantCode   UNION  select count(1)  as total from ms_identity_account c where c.merchant_code=:merchantCode   ) t where t.total>0", param);
    }
    catch (EmptyResultDataAccessException e) {}
    return 0;
  }
  
  public MerchantVO getMerchantSignalCode(String signalCode)
    throws BusinessException
  {
    try
    {
      MerchantVO param = new MerchantVO();
      param.setSignalCode(signalCode);
      return (MerchantVO)queryForObject("select  mm.merchant_code, mm.signal_code,mm.short_name,mm.name,mm.is_self,mm.bal_type,mm.address,mm.post,mm.owner,mm.id_no,mm.telphone,mm.contact_name,mm.state,mm.district_code,mm.check_date,mm.created_by,mm.created_time,mm.updated_by,mm.updated_time,mm.is_valid,mm.provider_type,mm.alipay,mm.email,mm.transmatic from ms_merchant mm  where  mm.signal_code=:signalCode ", param, MerchantVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      return null;
    }
    catch (IncorrectResultSizeDataAccessException e)
    {
      throw new BusinessException("根据运营商代码查询运营商,查询为多个结果[signalCode=" + signalCode + "]");
    }
  }
}
